# 创建医院列表，通过查询主单，判断医院的一些信息
import json
import pandas as pd
from jbar import bar
from sqlalchemy import create_engine, text
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
import dotenv
from config import create_db_engine, load_env_hsp_abbr
# 创建数据库引擎
engine = create_db_engine()
import decimal

# 获取所有的医院别名列表
hspListRaw = load_env_hsp_abbr()

# 从 m 表中统计各个医院的数据，要有医院编码、名称、别名、病例数、人数、总金额、医保内金额
sql = """
SELECT
    hsp_code AS hsp_id, 
    substr(listagg(distinct hsp_name, ',') within group (order by hsp_name), 1, 4000) AS hsp_name, 
    max(hsp_abbr) AS hsp_abbr,
    max(j_hsp_lv) AS j_hsp_lv, 
    count(DISTINCT setl_id) AS case_count,
    count(DISTINCT psn_no) AS psn_no,
    sum(gnr_c) AS gnr_c,
    sum(gnr_b) AS gnr_b,
    sum(hifp_pay) AS hifp_pay,
    count(DISTINCT case when j_isin = '1' then setl_id end) AS isin_case_count,
    count(DISTINCT case when j_isin = '1' then psn_no end) AS isin_psn_count,
    sum(case when j_isin= '1' then gnr_c else 0 end) AS j_isin_c,
    sum(case when j_isin= '1' then gnr_b else 0 end) AS j_isin_b,
    count(DISTINCT case when j_isin = '0' then setl_id end) AS noisin_case_count,
    count(DISTINCT case when j_isin = '0' then psn_no end) AS noisin_psn_count,
    sum(case when j_isin= '0' then gnr_c else 0 end) AS j_noisin_c,
    sum(case when j_isin= '0' then gnr_b else 0 end) AS j_noisin_b
FROM m
GROUP BY hsp_code
"""

# 执行 SQL 查询
with engine.connect() as conn:
    result = conn.execute(text(sql))
    # 将结果转化为 list，每一行是一个字典，然后用json保存下来
    data = [dict(row._mapping) for row in result]
    # 仅保留 hspCode 在 hspListRaw 中的医院
    hsp_codes_set = set(code for code, abbr in hspListRaw)
    data = [row for row in data if row['hsp_id'] in hsp_codes_set]

def decimal_default(obj):
    if isinstance(obj, decimal.Decimal):
        return float(obj)
    raise TypeError

with open('hspList.json', 'w', encoding='utf-8') as f:
    json.dump(data, f, ensure_ascii=False, indent=4, default=decimal_default)